﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Windows.Forms;
using NmbpProject2.DboClasses;
using Oracle.DataAccess.Client;

namespace NmbpProject2.DataLayer
{
    internal class CompetitionDL
    {

        /// <summary>
        /// vraca listu svih natjecanja iz baze
        /// edit:zak
        /// dodana funkcionalnost za oracle
        /// </summary>
        /// <returns></returns>
        public List<Competition> allCompetitions(string test)
        {
            var compList = new List<Competition>();
            switch (DbConfig.DBTYPE)
            {
                case dbtype.Db40:
                    var compListDb = DataConfig.db.QueryByExample(typeof(Competition));
                    foreach (Competition competition in compListDb)
                    {
                        compList.Add(competition);
                    }
                    break;
                case dbtype.GeoSpatial:
                    break;
                case dbtype.Oracle:
                    var oracle = new OracleConnectionClass();
                    oracle.openConnection();
                    var cmd = new OracleCommand("SELECT * FROM competitions where lower(name) like lower('%"+test+"%')", oracle.conn);
                    cmd.CommandType = CommandType.Text;
                    var dt = new DataTable();
                    new OracleDataAdapter(cmd).Fill(dt);
                    foreach (DataRow row in dt.Rows)
                    {
                        string name = row[0].ToString();
                        DateTime date = (DateTime)(row[1]);
                        var progList = ProgLanguageDL.getAllLanguages("competitionslanguages",
                                                            string.Format("deref(competition).name like '{0}'", name));
                        var catList = CategoryDL.getAllCategories("competitionscategories",
                                                            string.Format("deref(competition).name like '{0}'", name));
                        compList.Add(new Competition(name, date, catList, progList));

                    }
                    oracle.closeConnection();
                    break;
                case dbtype.Xml:
                    break;
            }
            return compList;
        }


        /// <summary>
        /// stvara novo natjecanje i pohranjuje ga u bazu
        /// s zadanim parametrima
        /// edit:zak
        /// radi i za oracle
        /// </summary>
        /// <param name="name"></param>
        /// <param name="date"></param>
        /// <param name="categories"></param>
        /// <param name="progLanguages"></param>
        public void newCompetition(string name, DateTime date, List<Category> categories,
                                   List<ProgLanguage> progLanguages)
        {
            switch (DbConfig.DBTYPE)
            {
                case dbtype.Db40:
                    var comp = new Competition(name, date, categories, progLanguages);
                    DataConfig.db.Store(comp);
                    break;
                case dbtype.GeoSpatial:
                    break;
                case dbtype.Oracle:
                    try
                    {
                        var oracle = new OracleConnectionClass();
                        oracle.openConnection();
                        var cmd = new OracleCommand();
                        cmd.Connection = oracle.conn;
                        cmd.CommandType = CommandType.Text;
                        cmd.CommandText = String.Format(
                            "insert into competitions values ('{0}', to_date('{1}','dd.mm.yyyy.'))",
                            name, date.ToString("dd.MM.yyyy."));
                        cmd.ExecuteNonQuery();
                        foreach (var language in progLanguages)
                        {
                            cmd.CommandText = string.Format("insert into competitionslanguages " +
                            "select ref(comp), ref (lang) from competitions comp, languages lang " +
                            "where (comp.name='{0}') and (comp.dateheld = to_date('{1}','dd.mm.yyyy.')) and (lang.name='{2}')",
                            name, date.ToString("dd.MM.yyyy."), language.Name);
                            cmd.ExecuteNonQuery();
                        }
                        foreach (var category in categories)
                        {
                            cmd.CommandText = string.Format("insert into competitionscategories " +
                            "select ref(comp), ref (cat) from competitions comp, categories cat  " +
                            "where (comp.name='{0}') and (comp.dateheld = to_date('{1}','dd.mm.yyyy.')) and (cat.name='{2}')",
                            name, date.ToString("dd.MM.yyyy."), category.Name);
                            cmd.ExecuteNonQuery();
                        }
                        oracle.closeConnection();
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message, "ERROR");
                    }
                    break;
                case dbtype.Xml:
                    break;
            }

        }

        /// <summary>
        /// update natjecanja zadanim parametrinam
        /// </summary>
        /// <param name="name"></param>
        /// <param name="date"></param>
        /// <param name="categories"></param>
        /// <param name="progLanguages"></param>
        /// <param name="competition"></param>
        public void updateCompetition(string name, DateTime date, List<Category> categories,
                                      List<ProgLanguage> progLanguages, Competition competition)
        {
            switch (DbConfig.DBTYPE)
            {
                case dbtype.Db40:
                    var compUpdated = (Competition)DataConfig.db.QueryByExample(competition)[0];
                    compUpdated.Name = name;
                    compUpdated.Date = date;
                    compUpdated.Categories = categories;
                    compUpdated.ProgLanguages = progLanguages;
                    DataConfig.db.Store(compUpdated);
                    break;
                case dbtype.GeoSpatial:
                    break;
                case dbtype.Oracle:
                    try
                    {
                        var oracle = new OracleConnectionClass();
                        oracle.openConnection();
                        var cmd = new OracleCommand();
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = oracle.conn;
                        string where =
                            string.Format(
                                " where deref(competition).name = '{0}' and deref(competition).dateheld = to_date('{1}','dd.mm.yyyy.')",
                                competition.Name, competition.Date.ToString("dd.MM.yyyy."));
                        //deletes
                        cmd.CommandText = string.Format("delete from competitionscategories{0}", where);
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = string.Format("delete from competitionslanguages{0}", where);
                        cmd.ExecuteNonQuery();
                        //inserts
                        foreach (var language in progLanguages)
                        {
                            cmd.CommandText = string.Format("insert into competitionslanguages " +
                            "select ref(comp), ref (lang) from competitions comp, languages lang " +
                            "where (comp.name='{0}') and (comp.dateheld = to_date('{1}','dd.mm.yyyy.')) and (lang.name='{2}')",
                            competition.Name, competition.Date.ToString("dd.MM.yyyy."), language.Name);
                            cmd.ExecuteNonQuery();
                        }
                        foreach (var category in categories)
                        {
                            cmd.CommandText = string.Format("insert into competitionscategories " +
                            "select ref(comp), ref (cat) from competitions comp, categories cat  " +
                            "where (comp.name='{0}') and (comp.dateheld = to_date('{1}','dd.mm.yyyy.')) and (cat.name='{2}')",
                            competition.Name, competition.Date.ToString("dd.MM.yyyy."), category.Name);
                            cmd.ExecuteNonQuery();
                        }
                        //update
                        cmd.CommandText =
                            string.Format(
                                "update competitions set name = '{0}', dateheld = to_date('{1}','dd.mm.yyyy.') where (name='{2}' and dateheld = to_date('{3}','dd.mm.yyyy.'))",
                                name, date.ToString("dd.MM.yyyy."), competition.Name,competition.Date.ToString("dd.MM.yyyy."));
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                    }
                    break;
                case dbtype.Xml:
                    break;
            }
        }
        ///// <summary>
        ///// radi update predanog natjecanja
        ///// edit:Zak
        ///// 
        ///// </summary>
        ///// <param name="competition"></param>
        //public void updateCompetition(Competition competition)
        //{
        //    switch (DbConfig.DBTYPE)
        //    {
        //        case dbtype.Db40:
        //            DataConfig.db.Store(competition);
        //            break;
        //        case dbtype.GeoSpatial:
        //            break;
        //        case dbtype.Oracle:
        //            try
        //            {

        //            }
        //            catch (Exception e)
        //            {


        //            }
        //            break;
        //        case dbtype.Xml:
        //            break;
        //    }
        //}
       

        /// <summary>
        /// brise zadano natjecanje iz baze,
        /// te sve prijave povezane s natjecanjem(sadrzavanje)
        /// edit:zak
        /// dodana funkcionalnost za oracle
        /// </summary>
        /// <param name="competition"></param>
        public void deleteCompetition(Competition competition)
        {
            switch (DbConfig.DBTYPE)
            {
                case dbtype.Db40:
                    foreach (CompApplication application in competition.Applications)
                    {
                        DataConfig.db.Delete(application);
                    }
                    DataConfig.db.Delete(competition);
                    break;
                case dbtype.GeoSpatial:
                    break;
                case dbtype.Oracle:
                    try
                    {
                        var oracle = new OracleConnectionClass();
                        oracle.openConnection();
                        var cmd = new OracleCommand();
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = oracle.conn;
                        string where =
                            string.Format(
                                " where deref(competition).name = '{0}' and deref(competition).dateheld = to_date('{1}','dd.mm.yyyy.')",
                                competition.Name, competition.Date.ToString("dd.MM.yyyy."));
                        cmd.CommandText = string.Format("delete from applications{0}", where);
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = string.Format("delete from competitionscategories{0}", where);
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = string.Format("delete from competitionslanguages{0}", where);
                        cmd.ExecuteNonQuery();
                        cmd.CommandText = string.Format("delete from competitions where name='{0}' and dateheld=to_date('{1}','dd.mm.yyyy.')",
                                                        competition.Name, competition.Date.ToString("dd.MM.yyyy."));
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        e.Message.ToString();
                    }
                    break;
                case dbtype.Xml:
                    break;
            }
        }

        /// <summary>
        /// provjera postoji li vec identicno natjecanje u bazi
        /// radi se po imenu i datumu.
        /// </summary>
        /// <param name="competition"></param>
        /// <returns></returns>
        public bool isDuplicate(Competition competition)
        {
            switch (DbConfig.DBTYPE)
            {
                case dbtype.Db40:
                    var compList = DataConfig.db.Query(
                        (Competition match) => (match.Name == competition.Name) && (match.Date == competition.Date));
                    return compList.Count != 0;
                case dbtype.GeoSpatial:
                    break;
                case dbtype.Oracle:
                    //try
                    //{
                    //    var oracle = new OracleConnectionClass();
                    //    oracle.openConnection();
                    //    var com = new OracleCommand();

                    //}
                    //catch (Exception e)
                    //{
                    //    e.Message.ToString();
                    //}
                    break;
                case dbtype.Xml:
                    break;
            }
            return false;
        }

        public List<Competition> findByLanguage(ProgLanguage language, bool onlyLanguage)
        {

            switch (DbConfig.DBTYPE)
            {
                case dbtype.Db40:
                    IList<Competition> result = DataConfig.db.Query(delegate(Competition match)
                                                                        {
                                                                            if (onlyLanguage)
                                                                            {
                                                                                if (match.ProgLanguages.Count == 1)
                                                                                {
                                                                                    return match.ProgLanguages[0] == language;
                                                                                }
                                                                                else
                                                                                {
                                                                                    return false;
                                                                                }
                                                                            }
                                                                            else
                                                                            {
                                                                                foreach (
                                                                                    ProgLanguage lang in match.ProgLanguages)
                                                                                {
                                                                                    if (lang == language)
                                                                                    {
                                                                                        return true;
                                                                                    }
                                                                                }
                                                                                return false;
                                                                            }
                                                                        });
                    if (result.Count == 0)
                    {
                        return null;
                    }
                    else
                    {
                        List<Competition> competitions = new List<Competition>();
                        foreach (Competition competition in result)
                        {
                            competitions.Add(competition);
                        }
                        return competitions;
                    }
                case dbtype.GeoSpatial:
                    break;
                case dbtype.Oracle:
                    break;
                case dbtype.Xml:
                    break;
            }
            return null;
        }

        public List<Competition> findByCategory(Category category, bool onlyCategory)
        {
            switch (DbConfig.DBTYPE)
            {
                case dbtype.Db40:
                    IList<Competition> result = DataConfig.db.Query(delegate(Competition match)
                                                                        {
                                                                            if (onlyCategory)
                                                                            {
                                                                                if (match.Categories.Count == 1)
                                                                                {
                                                                                    return match.Categories[0] == category;
                                                                                }
                                                                                else
                                                                                {
                                                                                    return false;
                                                                                }
                                                                            }
                                                                            else
                                                                            {
                                                                                foreach (Category cat in match.Categories)
                                                                                {
                                                                                    if (cat == category)
                                                                                    {
                                                                                        return true;
                                                                                    }
                                                                                }
                                                                                return false;
                                                                            }
                                                                        });
                    if (result.Count == 0)
                    {
                        return null;
                    }
                    else
                    {
                        List<Competition> competitions = new List<Competition>();
                        foreach (Competition competition in result)
                        {
                            competitions.Add(competition);
                        }
                        return competitions;
                    }
                    break;
                case dbtype.GeoSpatial:
                    break;
                case dbtype.Oracle:
                    break;
                case dbtype.Xml:
                    break;
            }
            return null;
        }


    }
}